﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;
using System.Data;

using CSharpProjectLayers.Models;

namespace CSharpProjectLayers.DAL
{
    /// <summary>
    /// 员工信息数据访问类
    /// </summary>
    public class EmployeeService
    {
        /*        #region 参数太多，使用起来非常不便利
                public int AddEmployee(string EployName, string gender,string NowAddress, string IdNo, string WeiXinNumber, string PhoneNumber, string OtherWork, DateTime EntryDate, int PostId, int DepartmentId)
                {
                    //定义sql语句
                    string sql = "insert into Employee(EployName, Gender, NowAddress, IdNo,WeiXinNumber,PhoneNumber, OtherWork, EntryDate, PostId, DepartmentId)";
                    sql += " values(@EployName, @Gender, @NowAddress, @IdNo, @WeiXinNumber, @PhoneNumber, @OtherWork, @EntryDate, @PostId, @DepartmentId)";
                    //封装参数
                    SqlParameter[] param = new SqlParameter[]
                    {
                        new SqlParameter( "@EployName", EployName ),
                        new SqlParameter( "@gender", gender ),
                        new SqlParameter( "@NowAddress", NowAddress ),
                        new SqlParameter( "@IdNo", IdNo ),
                        new SqlParameter( "@WeiXinNumber", WeiXinNumber ),
                        new SqlParameter( "@PhoneNumber", PhoneNumber ),
                        new SqlParameter( "@OtherWork", OtherWork ),
                        new SqlParameter( "@EntryDate", EntryDate.ToShortDateString()),
                        new SqlParameter( "@PostId", PostId ),
                        new SqlParameter( "@DepartmentId", DepartmentId ),
                    };
                    //调用通用类
                    return SQLHelper.ExecuteNonQuery(sql, param);
                }
            }
            #endregion  */
        public int AddEmployee(Employee employee)
        {
            //定义sql语句
            string sql = "insert into Employee(EployName, Gender, NowAddress, IdNo,WeiXinNumber,PhoneNumber, OtherWork, EntryDate, PostId, DepartmentId)";
            sql += " values(@EployName, @Gender, @NowAddress, @IdNo, @WeiXinNumber, @PhoneNumber, @OtherWork, @EntryDate, @PostId, @DepartmentId)";
            //封装参数
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter( "@EployName", employee.EployName ),
                new SqlParameter( "@Gender", employee.Gender ),
                new SqlParameter( "@NowAddress", employee.NowAddress ),
                new SqlParameter( "@IdNo", employee.IdNo ),
                new SqlParameter( "@WeiXinNumber", employee.WeiXinNumber ),
                new SqlParameter( "@PhoneNumber", employee.PhoneNumber ),
                new SqlParameter( "@OtherWork", employee.OtherWork ),
                new SqlParameter( "@EntryDate", employee.EntryDate.ToShortDateString()),
                new SqlParameter( "@PostId", employee.PostId ),
                new SqlParameter( "@DepartmentId", employee.DepartmentId ),
            };
            //调用通用类
            return SQLHelper.ExecuteNonQuery(sql, param);
        }

        /// <summary>
        /// 根据部门编号查询员工信息
        /// </summary>
        /// <param name="DepartmentId"></param>
        /// <returns></returns>
        public List<Employee> QueryEmployeeByDepartmentId(string departmentId)
        {
            string sql = "select EployName,Gender,NowAddress,IdNo,WeiXinNumber,PhoneNumber from Employee";
            sql += " where DepartmentId = @DepartmentId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@DepartmentId", departmentId )
            };
            //提交查询
            SqlDataReader reader = SQLHelper.ExecuteReader(sql,param);
            //封装对象到集合中
            List<Employee> eList = new List<Employee>();
            while(reader.Read())
            {
                eList.Add(new Employee
                {
                    EployName = reader["EployName"].ToString(),
                    Gender = reader["Gender"].ToString(),
                    NowAddress = reader["NowAddress"].ToString(),
                    IdNo = reader["IdNo"].ToString(),
                    WeiXinNumber = reader["WeiXinNumber"].ToString(),
                    PhoneNumber = reader["PhoneNumber"].ToString(),
                });
            }
            reader.Close();
            return eList;
        }
    }
}
